package com.xingyi.bacs.service;

import com.xingyi.bacs.dao.ExcelToOracleDao;
import com.xingyi.bacs.util.StringUtil;
import com.xingyi.bacs.vo.ResponeBodyVo;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wujt on 8/9/2022
 */
@Service
public class ExcelToOracleServiceImp implements ExcelToOracleService{

    @Resource
    private ExcelToOracleDao excelToOracleDao;

    @Override
    public ResponeBodyVo dealExcel(MultipartFile file, String data, String tableName, String seqName) {
        Map<String,String> map = new HashMap<>();//execl字段名->数据库字段名
        Map<String,String> map2 = new HashMap<>();//数据库字段名->字段类型
        String[] strArr = data.split("&&");
        for (String str : strArr){
            String[] arr = str.split(">>>");
            String[] arr2 = arr[1].split("-->");
            //arr[0]是excel字段名     arr2[0]是数据库字段名      arr2[1]是字段类型
            map.put(arr[0].trim(),arr2[0].trim());
            map2.put(arr2[0].trim(),arr2[1].trim());
        }
        String fileName = file.getOriginalFilename();
        String prefix=fileName.substring(fileName.lastIndexOf(".")+1);
        if (prefix.equals("xlsx")) {
            // 调用read2007
            return readNegativeCancelDataXlsx(file, map, map2, tableName, seqName);
        } else {
            // 调用read2003
            return readNegativeCancelDataXls(file, map, map2, tableName, seqName);
        }
    }

    private ResponeBodyVo readNegativeCancelDataXlsx(MultipartFile file,
        Map<String,String> map, Map<String,String> map2, String tableName, String seqName) {
        ResponeBodyVo responeBodyVo = new ResponeBodyVo();
        InputStream is = null;
        List<String> propertyList = new ArrayList<>();//excel字段
        List<String> columnList = new ArrayList<>();//数据库字段
        List<String> columnTypeList = new ArrayList<>();//字段类型
        List<Object[]> list = new ArrayList<>();//excel数据
        Object[] objects = null;
        try{
            for(Map.Entry<String, String> entry:map.entrySet()){
                propertyList.add(entry.getKey());
            }
            is = file.getInputStream();
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            XSSFRow property = xssfSheet.getRow(0);//获取字段行
            //对columnList进行重排序
            for (int i=0; i<propertyList.size(); i++){
                columnList.add(map.get(property.getCell(i).toString().trim()));
                columnTypeList.add(map2.get(map.get(property.getCell(i).toString().trim())));
            }
            for (int i = 1; i <= xssfSheet.getLastRowNum(); i++) {//循环打印Excel表中的内容
                XSSFRow xssfRow = xssfSheet.getRow(i);
                objects = new Object[propertyList.size()];
                for (int j=0; j<propertyList.size(); j++){
                    objects[j] = xssfRow.getCell(j);
                }
                list.add(objects);
            }
            excelToOracleDao.dealExcel(columnList,columnTypeList,list,tableName,seqName);
            responeBodyVo.setErrorCode(ResponeBodyVo.SUCCESS);
            responeBodyVo.setErrorMsg("成功啦...");
        }catch (Exception e){
            responeBodyVo.setErrorCode(ResponeBodyVo.ERROR);
            responeBodyVo.setErrorMsg(e.getMessage());
        }
        return responeBodyVo;
    }

    private ResponeBodyVo readNegativeCancelDataXls(MultipartFile file,
        Map<String,String> map, Map<String,String> map2, String tableName, String seqName) {
        ResponeBodyVo responeBodyVo = new ResponeBodyVo();
        InputStream is = null;
        List<String> propertyList = new ArrayList<>();//excel字段
        List<String> columnList = new ArrayList<>();//数据库字段
        List<String> columnTypeList = new ArrayList<>();//字段类型
        List<Object[]> list = new ArrayList<>();//excel数据
        Object[] objects = null;
        try{
            for(Map.Entry<String, String> entry:map.entrySet()){
                propertyList.add(entry.getKey());
            }
            is = file.getInputStream();
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            HSSFRow property = hssfSheet.getRow(0);//获取字段行
            //对columnList进行重排序
            for (int i=0; i<propertyList.size(); i++){
                columnList.add(map.get(property.getCell(i).toString().trim()));
                columnTypeList.add(map2.get(map.get(property.getCell(i).toString().trim())));
            }
            for (int i = 1; i <= hssfSheet.getLastRowNum(); i++) {//循环打印Excel表中的内容
                HSSFRow hssfRow = hssfSheet.getRow(i);
                objects = new Object[propertyList.size()];
                for (int j=0; j<propertyList.size(); j++){
                    objects[j] = hssfRow.getCell(j);
                }
                //剔除excel中的空行
                if (isNullString(objects)){
                    continue;
                }
                list.add(objects);
            }
            excelToOracleDao.dealExcel(columnList,columnTypeList,list,tableName,seqName);
            responeBodyVo.setErrorCode(ResponeBodyVo.SUCCESS);
            responeBodyVo.setErrorMsg("成功啦...");
        }catch (Exception e){
            responeBodyVo.setErrorCode(ResponeBodyVo.ERROR);
            responeBodyVo.setErrorMsg(e.getMessage());
        }
        return responeBodyVo;
    }

    /**
     * excel空行判断
     * @param obj
     * @return
     */
    private boolean isNullString(Object[] obj){
        for (Object o : obj){
            if (StringUtil.hasValue(o)){
                return false;
            }
        }
        return true;
    }
}
